<?php

namespace app\api\controller;

use addons\qcloudsms;
use app\common\controller\Api;
use think\Db;

/**
 * 示例接口
 */
class Dahui extends Api
{

    // 无需登录的接口,*表示全部
    protected $noNeedLogin = ['*'];
    // 无需鉴权的接口,*表示全部
    protected $noNeedRight = ['*'];

    protected $needsendsms = 0;//需要发短信

    public function _initialize()
    {
        $sys = \app\admin\model\Syssetting::get(1);
        if ($sys) {
            $this->needsendsms = $sys->switch;
        }

        parent::_initialize();
    }

    //http://dahui.test/api/dahui/test
    public function test()
    {
        $qcloudsms = new \addons\qcloudsms\Qcloudsms();
        $params = array('type' => 0, 'mobile' => "13564508883", 'msg' => "恭喜发财");
        $qcloudsms->smsSend($params);

        $clsms = new \addons\clsms\Clsms();
        $params = array('mobile' => "13564508883", 'code' => "abca");
        $clsms->smsSend($params);


        $this->success('返回成功', $this->request->param());
    }

    //http://dahui.test/api/dahui/test2
//http://jk.106api.cn/smsGBK.aspx?type=send&username=465979204&password=A7D2D07BEEA0BB4025E6C9A804D31707&gwid=51998&mobile=13564508883&message=【上海社保通】您的验证码是：23241
    public function test2()
    {
//        require '\'APP_PATH\', __DIR__ .addons/smsdx/smsdx.php';

        $username = "465979204";
        $password = "A7D2D07BEEA0BB4025E6C9A804D31707";
        $gwid = "51998";
        $mobile = "13564508883";
        $message = '【上海社保通】您的验证码是：2341';

        $postData = array
        (
            'type' => 'send',
            'username' => $username,
            'password' => $password,
            'gwid' => $gwid,
            'mobile' => $mobile,
            'message' => $message
        );
        $url = "http://jk.106api.cn/smsUTF8.aspx";
        $result = $this->postSMS($url, $postData);

        $this->success('返回成功', $result);
    }

    function postSMS($url, $postData)
    {
        $row = parse_url($url);
        $host = $row['host'];
        $port = isset($row['port']) ? $row['port']:80;
        $file = $row['path'];
        $post = "";

        foreach ($postData as $k => $v) {
            if ($v === '' || $v === null) {
                continue;
            } else {
                $post .= rawurlencode($k)."=".rawurlencode($v)."&";
            }
        }

        $post = substr( $post , 0 , -1 );
        $len = strlen($post);
        $fp = @fsockopen( $host ,$port, $errno, $errstr, 10);
        if (!$fp) {
            return "$errstr ($errno)\n";
        } else {
            $receive = '';
            $out = "POST $file HTTP/1.1\r\n";
            $out .= "Host: $host\r\n";
            $out .= "Content-type: application/x-www-form-urlencoded\r\n";
            $out .= "Connection: Close\r\n";
            $out .= "Content-Length: $len\r\n\r\n";
            $out .= $post;
            fwrite($fp, $out);
            while (!feof($fp)) {
                $receive .= fgets($fp, 128);
            }
            fclose($fp);
            $receive = explode("\r\n\r\n",$receive);
            unset($receive[0]);
            return implode("",$receive);
        }
    }

    //http://dahui.test/api/dahui/test3
    public function test3()
    {
//        $order = new \app\admin\model\dahui\Dingdan;
//        $list = $order->where('businesstype', '个税')
//            ->where('idnumber', '350782198905060032')
//            ->where('paymentdate', '2019-05')
//            ->limit(1)
//            ->select();
//        $total = $order
//            ->where('businesstype', '个税2')
//            ->where('idnumber', '350782198905060032')
//            ->where('paymentdate', '2019-05')
//            ->count();

//        $yw = \app\admin\model\dahui\Yewu::where('businesstype', '个税')
//            ->where('idnumber', '654323199411240049')
//            ->find();

        $ny = \app\admin\model\dahui\Nianyue::where('businessnumber', 'd186bcdc-5f39-4fd2-a468-59f143ef16f3')
            ->field('paymentdate')
            ->limit(1)
            ->find()->toArray();


        $yw = \app\admin\model\dahui\Yewu::where('id', 11521)
            ->field('id,businessnumber,businesstype,idnumber,paymentnumber,agencyfees')
            ->find();
        $yw = $yw->toArray();

        $list0 = \app\admin\model\dahui\Dingdan::where('taobaoname', '-jjjj-')
            ->field('idnumber')
            ->group("idnumber")
            ->select();

        $list = [];
        foreach ($list0 as $row0) {
            $ywlist = \app\admin\model\dahui\Yewu::where('idnumber', $row0['idnumber'])
                ->field('businessnumber,businesstype,idnumber,paymentnumber,agencyfees')
                ->select();

            foreach ($ywlist as &$yw) {
                $yw['kehu'] = \app\admin\model\dahui\Kehu::where('idnumber', $yw['idnumber'])
                    ->field('name,mobile')
                    ->find();

                $yw['nianyue'] = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                    ->where('refundornot', '<>', "已退款")
                    ->order('paymentdate', 'desc')
                    ->field('paymentdate,payingcompany,paymentbase,paymentamount,refundornot,zc_status')
                    ->limit(1)
                    ->find();

                $list[] = $yw;
                break;
            }
        }

//        $ny = \app\admin\model\dahui\Nianyue::where('businessnumber', 'fff3788c-474d-4d70-a343-ca9cff17a62d')
//            ->where('refundornot', '<>', "已退款")
//            ->order('paymentdate', 'desc')
//            ->field('paymentdate,payingcompany,paymentbase,paymentamount,refundornot,zc_status')
//            ->find();

        $this->success('返回成功', $list);
    }

    //http://dahui.test/api/dahui/test4
    public function test4() {

//        $paymentdate = date("Y-m");
        $paymentdate = "2030-01";

        $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
        $sql1 = "select count(*) as num ";
        $sql =  "from fa_dahui_nianyue ny LEFT JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
            ."LEFT JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber"
            ." where ny.paymentdate = '" . $paymentdate . "' ";

        if (!empty($businesstype)) {
            $sql .= " and yw.businesstype = '" . $businesstype . "' ";
        }
        if (!empty($idnumber)) {
            $sql .= " and yw.idnumber = '" . $idnumber . "' ";
        }

        if (!empty($newjoin_status)) {
            $sql .= " and ny.newjoin_status = '" . $newjoin_status . "' ";
        }
        if (!empty($payingcompany)) {
            $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
        }
        if (!empty($paymentnumber)) {
            $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
        }

        $sql .= " and ny.refundornot <> '已退款' and ny.zc_status <> '已办理'";

        $total = Db::query($sql1.$sql);

        $sql .= " limit 2 offset 0";
        $list = Db::query($sql0.$sql);

        $this->success('返回成功', $total[0]['num']);

    }

    //http://dahui.test/api/dahui/test6
    public function test6() {

//        $premoth = date("Y-m", strtotime("-1 months", strtotime("2019-01")));

        $thismoth = '2019-08';
        $nextmoth = date("Y-m", strtotime("+1 months", strtotime($thismoth)));

//        $xishu = \app\admin\model\dahui\Qiyegongzixishu::get(1)->toArray();
//        unset($xishu['id']);
//        unset($xishu['create_time']);
//        unset($xishu['update_time']);
//        $xishu = array_values($xishu);


        $model = new \app\admin\model\dahui\Gongzi;
        $gz = $model
            ->with(['kehu'])
//            ->where('id', 8)  //不能用id字段
            ->where('Gongzi.id', 8)
            ->find();

        $list = $model
            ->with(['kehu'])
            ->where('Gongzi.idnumber', '220105199108200020')
            ->select();

        $this->success('返回成功', $list);
    }

    //http://dahui.test/api/dahui/xgjs0
    //批量转
    //两种更新方式，也看不出谁快
    //saveAll， 需有id字段，否则是新增
    public function xgjs0()
    {
        $paymentdate = '2021-06';

        $gsmodel = new \app\admin\model\dahui\Gongshi();
        $gslist = $gsmodel->field('id,businesstype,name,minimumcardinality,proportion')
            ->select();
        //--1--
        foreach ($gslist as &$row) {
            if ($row['name'] == '达慧A') {
                $row['name'] = '达慧';
            }
            $row->save();
        }

        //--2--
//        $gslist = collection($gslist)->toArray();
//        foreach ($gslist as &$row) {
//            unset($row['businesstype_text']);//需去除多余字段
//            if ($row['name'] == '达慧A') {
//                $row['name'] = '达慧';
//            }
//        }
//        $gsmodel->saveAll($gslist);

        $this->success('返回成功', $gslist);
    }

    //http://dahui.test/api/dahui/xgjs
    //批量转
    public function xgjs()
    {
        $paymentdate = '2021-06';

        $gsmodel = new \app\admin\model\dahui\Gongshi();
        $gslist = $gsmodel->field('businesstype,name,minimumcardinality,proportion')->select();
        $gslist = collection($gslist)->toArray();
        foreach ($gslist as &$row) {
            $row['miniamount'] = round($row['minimumcardinality'] * $row['proportion'], 1);
        }

        $nymodel = new \app\admin\model\dahui\Nianyue();
        $nylist = $nymodel
            ->field('id, payingcompany, paymentbase, paymentamount') //field需在with前，否则field就无效了
            ->with(['yewu'])
            ->where('paymentdate', '>=', $paymentdate)
            ->select();

        $businesstype = '';
        foreach ($nylist as &$ny) {
            $businesstype = $ny->yewu->businesstype;

            $minimumcardinality = 0;
            $miniamount = 0;
            foreach ($gslist as $row) {
                if ($row['name'] == $ny->payingcompany && $row['businesstype'] == $businesstype) {
                    $minimumcardinality = $row['minimumcardinality'];
                    $miniamount = $row['miniamount'];
                    break;
                }
            }
            if ($ny->paymentbase < $minimumcardinality) {
                $ny->paymentbase = $minimumcardinality;
                $ny->paymentamount = $miniamount;
                $ny->save();
            }
        }

        $this->success('返回成功', $businesstype);
    }


    //年月关联订单id
    //http://dahui.test/api/dahui/nydingdanid
    //只需要处理上个月开始的订单
    //只需要关联订单的一个缴纳年月
    public function nydingdanid() {

        set_time_limit(0); //执行时间无限
        ini_set('memory_limit', '-1'); //内存无限

        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no');

        $model = new \app\admin\model\dahui\Dingdan;
//        select count(*) from fa_dahui_dingdan where auditstatus = '已审核'   --11w
//select * from fa_dahui_dingdan where auditstatus = '已审核' and orderdate > '2019-08-01'
//        select * from fa_dahui_dingdan where auditstatus = '已审核'and newkehu = '是' and orderdate > '2019-08-01'   3347
//        select * from fa_dahui_dingdan where auditstatus = '已审核' and newkehu = '是' and orderdate > '2019-08-01' and paymentdate = '2019-09'   226
//        select count(*) from fa_dahui_nianyue where paymentdate >= '2019-09'
        //查询所有审核过的订单; 新客户的订单才需要加入
        echo '现在是0：' . date('H:i:s') . '<br>';
        $total = $model
            ->where('auditstatus', '已审核')
            ->where('newkehu', '是')
            ->where('orderdate', '>', '2019-09-01')
            ->order('orderdate', 'asc')
            ->count();

        echo $total.'<br>';
        sleep(1);
        echo '现在是1：' . date('H:i:s') . '<br>';

//        exit;

        $list = $model
            ->where('auditstatus', '已审核')
            ->where('newkehu', '是')
            ->where('orderdate', '>', '2019-09-01')
            ->order('orderdate', 'asc')
            ->field('id, businesstype, paymentdate, idnumber')
            ->select();

        echo '现在是2：' . date('H:i:s') . '<br>';

        $num = 0;
        $dataset = [];
        foreach ($list as $row) {
            $yw = \app\admin\model\dahui\Yewu::where('businesstype', $row['businesstype'])
                ->where('idnumber', $row['idnumber'])
                ->find();
            if (!$yw) {
                continue;
            }

            $dates = explode(" ~ ", $row['paymentdate']);
            $paymentdate = $dates[0];

            //只需要关联订单的一个缴纳年月
            //今后如果要看本月缴纳年月关联的订单，如果关联的是0，则需找上月的关联id
//            $paymentdate2 = $paymentdate;
//            if (count($dates) > 1) {
//                $paymentdate2 = $dates[1];
//            }

            for ($i = 0; $i < 1; $i++) {
                $ny = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw->businessnumber)
                    ->where('paymentdate', $paymentdate)
                    ->find();
                if (!$ny || $ny->dingdan_id > 0)
                    continue;

                $ny->dingdan_id = $row->id;
//                $ny->save();
                $dataset[] = ['id' => $ny->id, 'dingdan_id' => $row->id];

                $num++;
                if ($num % 1000 == 0) {
                    echo $num.',';
                    sleep(1);
                    $sql = $this->batchUpdate('fa_dahui_nianyue', $dataset, 'id');
                    Db::query($sql);
                    $dataset = [];
                }

                break;
            }
        }

        echo '<br>'.'现在是3：' . date('H:i:s') . '<br>';

//        $modelny = new \app\admin\model\dahui\Nianyue;
//        $modelny->saveAll($dataset);
        $sql = $this->batchUpdate('fa_dahui_nianyue', $dataset, 'id');
        Db::query($sql);
        echo '现在是4：' . date('H:i:s') . '<br>';

//        $this->success('返回成功', $list);
    }

    public function nydingdanidX() {

        set_time_limit(0); //执行时间无限
        ini_set('memory_limit', '-1'); //内存无限

        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no');

        $model = new \app\admin\model\dahui\Dingdan;
//        select count(*) from fa_dahui_dingdan where auditstatus = '已审核'   --11w
//select * from fa_dahui_dingdan where auditstatus = '已审核' and orderdate > '2019-08-01'
//        select * from fa_dahui_dingdan where auditstatus = '已审核'and newkehu = '是' and orderdate > '2019-08-01'   3347
//        select * from fa_dahui_dingdan where auditstatus = '已审核' and newkehu = '是' and orderdate > '2019-08-01' and paymentdate = '2019-09'   226
//        select count(*) from fa_dahui_nianyue where paymentdate >= '2019-09'
        //查询所有审核过的订单; 新客户的订单才需要加入
        echo '现在是0：' . date('H:i:s') . '<br>';
        $total = $model
            ->where('auditstatus', '已审核')
            ->where('newkehu', '是')
            ->where('orderdate', '>', '2019-09-01')
            ->order('orderdate', 'asc')
            ->count();

        echo $total.'<br>';
        sleep(1);
        echo '现在是1：' . date('H:i:s') . '<br>';

//        exit;

        $list = $model
            ->where('auditstatus', '已审核')
            ->where('newkehu', '是')
            ->where('orderdate', '>', '2019-09-01')
            ->order('orderdate', 'asc')
            ->field('id, businesstype, paymentdate, idnumber')
            ->select();

        echo '现在是2：' . date('H:i:s') . '<br>';

        $num = 0;
        $dataset = [];
        foreach ($list as $row) {
            $yw = \app\admin\model\dahui\Yewu::where('businesstype', $row['businesstype'])
                ->where('idnumber', $row['idnumber'])
                ->find();
            if (!$yw) {
                continue;
            }

            $dates = explode(" ~ ", $row['paymentdate']);
            $paymentdate = $dates[0];
            $paymentdate2 = $paymentdate;
            if (count($dates) > 1) {
                $paymentdate2 = $dates[1];
            }

            for ($i = 0; $i < 48; $i++) {
                $ny = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw->businessnumber)
                    ->where('paymentdate', $paymentdate)
                    ->find();
                if (!$ny || $ny->dingdan_id > 0)
                    continue;

                $ny->dingdan_id = $row->id;
//                $ny->save();
                $dataset[] = ['id' => $ny->id, 'dingdan_id' => $row->id];

                $num++;
                if ($num % 1000 == 0) {
                    echo $num.',';
                    sleep(1);
                    $sql = $this->batchUpdate('fa_dahui_nianyue', $dataset, 'id');
                    Db::query($sql);
                    $dataset = [];
                }

                $paymentdate = date("Y-m", strtotime("+1 months", strtotime($paymentdate)));
                if ($paymentdate > $paymentdate2) {
                    break;
                }
            }
        }

        echo '<br>'.'现在是3：' . date('H:i:s') . '<br>';

//        $modelny = new \app\admin\model\dahui\Nianyue;
//        $modelny->saveAll($dataset);
        $sql = $this->batchUpdate('fa_dahui_nianyue', $dataset, 'id');
        Db::query($sql);
        echo '现在是4：' . date('H:i:s') . '<br>';

//        $this->success('返回成功', $list);
    }

    //http://dahui.test/api/dahui/testecho
    public function testecho()
    {
        set_time_limit(0);
        ini_set('memory_limit', '-1'); //内存无限
        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no'); // 关键是加了这一行。
        echo '现在是：' . date('H:i:s') . '<br>';

        $total = \app\admin\model\dahui\Nianyue::where('paymentdate', '>=', '2010-09')
            ->field('id, paymentdate, dingdan_id')
            ->count();
        echo $total . '<br>';
        $step = ceil($total/10000);

        for($s = 0; $s < $step; $s++) {
            $page_size = 10000;
            $offset = $s * 10000;
            //select count(*) from fa_dahui_nianyue where paymentdate>= '2019-09'
            $nylist = \app\admin\model\dahui\Nianyue::where('paymentdate', '>=', '2010-09')
                ->field('id, paymentdate, dingdan_id')
                ->limit($offset, $page_size)
                ->select();

            echo '现在是0：' . date('H:i:s') . ' ~ '.$offset. '<br>';
            $dataset = [];
            foreach ($nylist as $row) {
                $dataset[] = ['id' => $row->id, 'dingdan_id' => 0];
            }
            echo '现在是1：' . date('H:i:s') . '<br>';

            $sql = $this->batchUpdate('fa_dahui_nianyue', $dataset, 'id');
            echo '现在是2：' . date('H:i:s') . '<br>';
            Db::query($sql);
        }

//tp5的方式
//        $modelny = new \app\admin\model\dahui\Nianyue;
//        $modelny->isUpdate(true)->saveAll($dataset);
//批量更新的SQL

        echo '现在是E：' . date('H:i:s') . '<br>';

//        $nylist = collection($nylist)->toArray();
//        $this->success('返回成功', $nylist);
//        echo \GuzzleHttp\json_encode($nylist);

//        echo $this->batchUpdate($dataset, 'id', ['parent_id' => 100, 'title' => 'A']);

    }

    //https://www.cnblogs.com/ldj3/p/9288187.html

    /**
     * 批量更新函数
     * @param $data array 待更新的数据，二维数组格式
     * @param array $params array 值相同的条件，键值对应的一维数组
     * @param string $field string 值不同的条件，默认为id
     * @return bool|string
     */
    function batchUpdate($mytable, $data, $field, $params = [])
    {
        if (!is_array($data) || !$field || !is_array($params)) {
            return false;
        }

        $updates = $this->parseUpdate($data, $field);
        $where = $this->parseParams($params);

        // 获取所有键名为$field列的值，值两边加上单引号，保存在$fields数组中
        // array_column()函数需要PHP5.5.0+，如果小于这个版本，可以自己实现，
        // 参考地址：http://php.net/manual/zh/function.array-column.php#118831
        $fields = array_column($data, $field);
        $fields = implode(',', array_map(function($value) {
            return "'".$value."'";
        }, $fields));

        $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", $mytable, $updates, $field, $fields, $where);

        return $sql;
    }

    /**
     * 将二维数组转换成CASE WHEN THEN的批量更新条件
     * @param $data array 二维数组
     * @param $field string 列名
     * @return string sql语句
     */
    function parseUpdate($data, $field)
    {
        $sql = '';
        $keys = array_keys(current($data));
        foreach ($keys as $column) {

            $sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
            foreach ($data as $line) {
                $sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
            }
            $sql .= "END,";
        }

        return rtrim($sql, ',');
    }

    /**
     * 解析where条件
     * @param $params
     * @return array|string
     */
    function parseParams($params)
    {
        $where = [];
        foreach ($params as $key => $value) {
            $where[] = sprintf("`%s` = '%s'", $key, $value);
        }

        return $where ? ' AND ' . implode(' AND ', $where) : '';
    }

    //http://dahui.test/api/dahui/testsql
    function testsql()
    {
        $idnumber = "330721199205086918";
        $ywlist = \app\admin\model\dahui\Yewu::query("SELECT `id`,`businessnumber`,`businesstype`,`idnumber`,`paymentnumber`,`agencyfees` FROM `fa_dahui_yewu` WHERE  `idnumber` = '{$idnumber}' ORDER BY CONVERT(`businesstype` USING gbk)");
//        echo "add";

        $this->success('返回成功', $ywlist);
    }

    //http://dahui.test/api/dahui/testX
    //测试新短信
//http://jk.106api.cn/smsGBK.aspx?type=send&username=465979204&password=A7D2D07BEEA0BB4025E6C9A804D31707&gwid=51998&mobile=13564508883&message=【上海社保通】您的验证码是：23241
    public function testX()
    {
//        $target = "http://api.yx.ihuyi.com/webservice/sms.php?method=Submit";
        $target = "http://106.ihuyi.com/webservice/sms.php?method=Submit";
        $mobile = '13564508883';//手机号码，多个号码请用,隔开

        $post_data = "account=C84233857&password=c03d241ab20893478876564c8c67d219&mobile=" . $mobile . "&content=" . rawurlencode("尊敬的阿道夫客户，感谢您选择上海社保通代缴业务，公积金业务代缴已经完成请于次月15-20日公积金官网查询。");
//查看用户名 登录用户中心->营销短信>产品总览->API接口信息->APIID
//查看密码 登录用户中心->营销短信>产品总览->API接口信息->APIKEY
        $gets = $this->xml_to_array($this->Post($post_data, $target));
//        if ($gets['SubmitResult']['code'] == 2) {
//            echo '提交成功';
//        }
//        if ($gets['code'] == 2) {
//            echo '提交成功';
//        }

        $this->success('返回成功', $gets);
    }

    function Post($curlPost, $url)
    {
//        header("Content-type:text/html; charset=UTF-8");

        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_HEADER, false);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($curl, CURLOPT_NOBODY, true);
        curl_setopt($curl, CURLOPT_POST, true);
        curl_setopt($curl, CURLOPT_POSTFIELDS, $curlPost);
        $return_str = curl_exec($curl);
        curl_close($curl);
        return $return_str;
    }

    function xml_to_array($xml)
    {
        $reg = "/<(\w+)[^-->]*>([\\x00-\\xFF]*)<\\/\\1>/";
        if (preg_match_all($reg, $xml, $matches)) {
            $count = count($matches[0]);
            for ($i = 0; $i < $count; $i++) {
                $subxml = $matches[2][$i];
                $key = $matches[1][$i];
                if (preg_match($reg, $subxml)) {
                    $arr[$key] = xml_to_array($subxml);
                } else {
                    $arr[$key] = $subxml;
                }
            }
        }
        return $arr;
    }

    //检查缴纳年月的缴纳基数和金额时候和订单一致
    //http://dahui.test/api/dahui/checkpaymentbase
    //http://dahui.021paiqian.com:83/api/dahui/checkpaymentbase

    //检查订单和缴费记录的缴纳基数不一致
    //http://dahui.021paiqian.com:83/api/dahui/checkpaymentbase
    public function checkpaymentbase()
    {
        set_time_limit(0); //执行时间无限
        ini_set('memory_limit', '-1'); //内存无限

        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no');

        echo '1现在是：' . date('H:i:s') . '<br>';

        $dingdanlist = \app\admin\model\dahui\Dingdan::where('orderdate', '>', '2019-10-01')->where('auditstatus', '已审核')->order('id desc')->select();

//        $dingdanlist = \app\admin\model\dahui\Dingdan::where('orderdate', '>', '2019-10-01')
//            ->where('auditstatus', '已审核')
//            ->where('taobaoname', '孙洁8')
//            ->where('businesstype', '公积金')
//            ->where('paymentdate', '2019-10')
//            ->where('idnumber', '320324198610010328')
//            ->order('id desc')
//            ->select();

        //会存在相同缴纳年月的审核订单，以后面的订单为主
        $xx = 0;
        $nynum = 0;
        $ddnum = 0;
        $nyids = [];//修改过的年月id
        foreach ($dingdanlist as $row) {
            $yw = \app\admin\model\dahui\Yewu::where('businesstype', $row['businesstype'])
                ->where('idnumber', $row['idnumber'])
                ->find();
            if (!$yw) {
                continue;
            }
            $ddnum++;

            $dates = explode(" ~ ", $row['paymentdate']);
            $paymentdate = $dates[0];
            $paymentdate2 = $paymentdate;
            if (count($dates) > 1) {
                $paymentdate2 = $dates[1];
            }

            for ($i = 0; $i < 48; $i++) {
                $ny = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw->businessnumber)
                    ->where('paymentdate', $paymentdate)
                    ->find();
                if (!$ny) {
                    echo '未找到缴纳记录--businessnumber='.$yw->businessnumber.' paymentdate='.$paymentdate. '<br>';
                    break;
                }
                $isdo = 0;
                $err0 = "";
                $err1 = "";
                $err2 = "";
                $err3 = "";

                if (in_array($ny->id, $nyids)) {
                    echo $ny->id . " 重复修改<br>";
                    break;
                }
                $nyids[] = $ny->id;

                if (intval($ny->paymentbase) != intval($row['paymentbase'])) {
                    $xx++;
                    $isdo = 1;
                    $err0 = 'ny_id=' . $ny->id . ' dingdanid=' . $row['id'] . ' ny_paymentbase=' . $ny->paymentbase . ' dingdan_paymentbase=' . $row['paymentbase'] . '-->' . $xx ;
                    echo $err0 . '<br>';
                }

                if (intval($ny->paymentamount) != intval($row['paymentamount'])) {
                    $err1 = 'ny_id=' . $ny->id . ' dingdanid=' . $row['id']. ' ny_paymentamount=' . $ny->paymentamount . ' dingdan_paymentamount=' . $row['paymentamount'];
                    echo $err1 . '<br>';
                    $isdo = 1;
                }

                //顺便检查缴纳公司为空的问题
                if (empty($ny->payingcompany)) {
                    $ny->payingcompany = $row['payingcompany'];
                    $err3 = 'ny_id=' . $ny->id . ' dingdanid=' . $row['id'] . ' payingcompany=' . $ny->payingcompany;
                    echo $err3 . '<br>';
                    $isdo = 1;
                }

                //
                if ($isdo > 0) {
                    $ny->paymentbase = $row['paymentbase'];
                    $ny->paymentamount = $row['paymentamount'];
                    $ny->save();

                    $err2 = $row->taobaoname."|".$row->businesstype."|".$row->idnumber."|".$row->paymentdate."|";
                    echo $err2 . '<br>';

                    $log = new \app\admin\model\Syslog();
                    $log->log = $err2 . " ## " . $err0 . " | " . $err1 . " | " . $err3;
                    $log->save();
                }

                $nynum++;
                $paymentdate = date("Y-m", strtotime("+1 months", strtotime($paymentdate)));
                if ($paymentdate > $paymentdate2) {
                    break;
                }
            }

        }

        echo '2完成：' . date('H:i:s') . ' | ny=' . $nynum . ' | dingdan=' . $ddnum . '<br>';
    }

    //http://dahui.test/api/dahui/test0
    //http://dahui.021paiqian.com:83/api/dahui/test0
    public function test0()
    {
        // 默认 id 升序
        $dingdanlist = \app\admin\model\dahui\Dingdan::where('orderdate', '>', '2019-10-01')
            ->where('auditstatus', '已审核')
            ->where('taobaoname', '孙洁8')
            ->where('businesstype', '公积金')
            ->where('paymentdate', '2019-10')
            ->where('idnumber', '320324198610010328')
            ->order('id desc')
            ->select();

        echo '<br>';

        $this->success('返回成功', $dingdanlist);
    }

    //检查缴纳年月和订单不一致
    //http://dahui.test/api/dahui/checkorderny
    //http://dahui.021paiqian.com:83/api/dahui/checkorderny
    public function checkorderny()
    {
        set_time_limit(0); //执行时间无限
        ini_set('memory_limit', '-1'); //内存无限

        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no');

        echo '1现在是：' . date('H:i:s') . '<br>';

        $dingdanlist = \app\admin\model\dahui\Dingdan::where('orderdate', '>', '2019-10-01')->where('auditstatus', '已审核')->select();

        foreach ($dingdanlist as $row) {
            $yw = \app\admin\model\dahui\Yewu::where('businesstype', $row['businesstype'])
                ->where('idnumber', $row['idnumber'])
                ->find();
            if (!$yw) {
                continue;
            }

            $dates = explode(" ~ ", $row['paymentdate']);
            $paymentdate = $dates[0];
            $paymentdate2 = $paymentdate;
            if (count($dates) > 1) {
                $paymentdate2 = $dates[1];
            }

            $nickname = "";
            for ($i = 0; $i < 48; $i++) {
                $ny = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw->businessnumber)
                    ->where('paymentdate', $paymentdate)
                    ->find();
                if (!$ny) {
                    $ny = new \app\admin\model\dahui\Nianyue;
                    $ny->businessnumber = $yw->businessnumber;
                    $ny->paymentdate = $paymentdate;
                    $ny->payingcompany = $row['payingcompany'];
                    $ny->paymentbase = $row['paymentbase'];
                    $ny->paymentamount = $row['paymentamount'];
                    $ny->taobaoname = $row['taobaoname'];
                    $ny->auditman = $nickname;
                    if ($i == 0) {
                        $ny->dingdan_id = $row->id;
                    }
                    $ny->save();
                    echo '少缴纳记录--businessnumber=' . $yw->businessnumber . ' paymentdate=' . $paymentdate . '--' . $i . '<br>';
                } else {
                    if (empty($nickname)) {
                        $nickname = $ny->auditman;
                    }
                }

                $paymentdate = date("Y-m", strtotime("+1 months", strtotime($paymentdate)));
                if ($paymentdate > $paymentdate2) {
                    break;
                }
            }

        }

        echo '2完成：' . date('H:i:s');
    }

    //计划发送短信-- 间隔5分钟
    //http://dahui.test/api/dahui/plansmssend
    //http://dahui.021paiqian.com:83/api/dahui/plansmssend
    public function plansmssend()
    {
        set_time_limit(0); //执行时间无限
        ini_set('memory_limit', '-1'); //内存无限

        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no');
        echo '1现在是：' . date('H:i:s') . '<br>';

        //10分钟前
        $thismonth = date("Y-m");
        $fromdate = date("Y-m-d H:i:s", strtotime("-20 minutes", strtotime(date('Y-m-d H:i:s'))));
        $todate = date("Y-m-d H:i:s", strtotime("-5 minutes", strtotime(date('Y-m-d H:i:s'))));
        $sql = "select ny.*, yw.businesstype, yw.idnumber, kehu.name as kehuname, kehu.mobile from fa_dahui_nianyue ny ";
        $sql .= " LEFT JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
            . " LEFT JOIN fa_dahui_kehu kehu on yw.idnumber = kehu.idnumber "
            . " where (ny.newjoin_status = '已办理' or ny.zc_status = '已办理') and (yw.businesstype='社保' or yw.businesstype='公积金') "
            . "and ny.update_time > '" . $fromdate . "' and ny.update_time < '".$todate."' and ny.paymentdate > '2019-10' ";

        $list = Db::query($sql);
        foreach ($list as $row) {
            if ($row['zc_status'] == '已办理') {
                $smslog = \app\admin\model\dahui\Smslog::where('nianyue_id', $row['id'])->where('sendtype', '转出成功')->find();
                if (!$smslog) {
                    $smslog = new \app\admin\model\dahui\Smslog();
                    $smslog->businesstype = $row['businesstype'];
                    $smslog->sendtype = '转出成功';
                    $smslog->idnumber = $row['idnumber'];
                    $smslog->mobile = $row['mobile'];
                    $smslog->nianyue_id = $row['id'];
                    $smslog->save();
                    echo $row['mobile'] . '|' . $row['idnumber'] . ' 转出成功<br>';
                    $this->sendmsg2($smslog->mobile, $smslog->businesstype, $row['kehuname']);
                }
            } else if ($row['newjoin_status'] == '已办理' && $thismonth == $row['paymentdate']) {

                $smslog = \app\admin\model\dahui\Smslog::where('nianyue_id', $row['id'])->where('sendtype', '新办成功')->find();
                if (!$smslog) {
                    $smslog = new \app\admin\model\dahui\Smslog();
                    $smslog->businesstype = $row['businesstype'];
                    $smslog->sendtype = '新办成功';
                    $smslog->idnumber = $row['idnumber'];
                    $smslog->mobile = $row['mobile'];
                    $smslog->nianyue_id = $row['id'];
                    $smslog->save();
                    echo $row['mobile'] . '|' . $row['idnumber'] . ' 新办成功<br>';
                    $this->sendmsg($smslog->mobile, $smslog->businesstype, $row['kehuname']);
                }
            }
            
        }

        echo '2完成：' . date('H:i:s');
    }

    //新入
    public function sendmsg($mobile, $businesstype, $name) {
        if ($this->needsendsms == 0) {
            return;
        }

//        $mobile = 13564508883;

        if ($businesstype == '公积金') {
            $message = "尊敬的" . $name . "客户，感谢您选择我司代缴社保公积金。公积金代缴业务已办理成功，请次月15至20日于公积金官网查询。";
        } else if ($businesstype == '社保') {
            $message = "尊敬的" . $name . "客户，感谢您选择我司代缴社保公积金。社保代缴业务已办理成功，请次月10至15日于就近社保中心查询。";
        } else {
            return;
        }


        $target = "http://106.ihuyi.com/webservice/sms.php?method=Submit";
        $post_data = "account=C84233857&password=c03d241ab20893478876564c8c67d219&mobile=" . $mobile . "&content=" . rawurlencode($message);
//查看用户名 登录用户中心->营销短信>产品总览->API接口信息->APIID
//查看密码 登录用户中心->营销短信>产品总览->API接口信息->APIKEY
        $gets = $this->xml_to_array($this->Post($post_data, $target));


    }

    //转出
    public function sendmsg2($mobile, $businesstype, $name) {

        if ($this->needsendsms == 0) {
            return;
        }
//        $mobile = 13564508883;

        if ($businesstype == '公积金') {
            $message = "尊敬的" . $name . "客户，感谢您选择我司代缴社保公积金。特此通知：您的公积金封存或转出业务已办理完成。祝您工作顺利，生活愉快！";
        } else if ($businesstype == '社保') {
            $message = "尊敬的" . $name . "客户，感谢您选择我司代缴社保公积金。特此通知：您的社保转出业务已办理完成。祝您工作顺利，生活愉快！";
        } else {
            return;
        }


        $target = "http://106.ihuyi.com/webservice/sms.php?method=Submit";
        $post_data = "account=C84233857&password=c03d241ab20893478876564c8c67d219&mobile=" . $mobile . "&content=" . rawurlencode($message);
//查看用户名 登录用户中心->营销短信>产品总览->API接口信息->APIID
//查看密码 登录用户中心->营销短信>产品总览->API接口信息->APIKEY
        $gets = $this->xml_to_array($this->Post($post_data, $target));


    }

    //http://dahui.test/api/dahui/testsendmsg
    public function testsendmsg() {
        if ($this->needsendsms == 0) {
            echo 'testsendmsg-未设置';
            return;
        }

        $message = "尊敬的林林客户，感谢您选择我司代缴社保公积金。公积金代缴业务已办理成功，请次月15至20日于公积金官网查询。";

        $target = "http://106.ihuyi.com/webservice/sms.php?method=Submit";
        $post_data = "account=C84233857&password=c03d241ab20893478876564c8c67d219&mobile=13564508883&content=" . rawurlencode($message);
//查看用户名 登录用户中心->营销短信>产品总览->API接口信息->APIID
//查看密码 登录用户中心->营销短信>产品总览->API接口信息->APIKEY
        $gets = $this->xml_to_array($this->Post($post_data, $target));

        echo '完成testsendmsg';
    }

//http://dahui.test/api/dahui/testXX
    public function testXX()
    {
        $bankcardnumber = "6217 7302 0270 6568";
        $bankcardnumber = $this->gshyhkh($bankcardnumber);
        echo $bankcardnumber;
    }

    //格式化银行卡号
    function gshyhkh($str)
    {
        $bankcardnumber = str_replace(" ", "", $str);
        $arr = str_split($bankcardnumber, 4);//4的意思就是bai每4个为一组
        $bankcardnumber = implode(' ', $arr);
        return $bankcardnumber;
    }

    //http://dahui.test/api/dahui/testDD
    public function testDD()
    {
//        $name = "uploads/robots.txt";
//        $this->downloads($name);

        $this->fileShow("uploads");
        echo "testDD";
    }

    public function downloads($fpath, $name)
    {
        if (substr($name, 0, 1) === '.') {
            return;
        }
        $file = fopen($fpath, "r");
        Header("Content-type: application/octet-stream");
        Header("Accept-Ranges: bytes");
        Header("Accept-Length: " . filesize($fpath));
        Header("Content-Disposition: attachment; filename=" . $name);
        echo fread($file, filesize($fpath));
        fclose($file);
        exit();
    }

    //遍历目录下的所有文件和文件夹
    function fileShow($dir)
    {
        $handle = opendir($dir);
        while ($file = readdir($handle)) {
            if ($file !== '..' && $file !== '.' && substr($file, 0, 1) !== '.') {
                $f = $dir . '/' . $file;
                if (is_file($f)) {
                    //文件
                    $this->downloads($f, $file);
                } else {
                    //文件夹
//                    $this->fileShow($f);
                }
            }
        }
    }

    //http://dahui.test/api/dahui/addneworder
    public function addneworder()
    {
        $params['orderdate'] = date('Y-m-d H:i:s');

        $total = $this->model
            ->where('businesstype', $params['businesstype'])
            ->where('idnumber', $params['idnumber'])
            ->where('auditstatus', '未审核')
            ->where('paymentdate', 'like', '%'.$params['paymentdate'].'%')
            ->count();

        if ($total > 0) {
            $this->error(__('系统已录入同一客户同一业务相同的未审核缴纳年月订单!'));
        }

        $dates = explode(" ~ ", $params['paymentdate']);

        $paymentdate = $dates[0];
        $paymentdate2 = $paymentdate;
        if (count($dates) > 1) {
            $paymentdate2 = $dates[1];
        }

        if (isset($params['taobaoname'])) {
            $params['taobaoname'] = trim($params['taobaoname']);
        }

        $yw = \app\admin\model\dahui\Yewu::where('businesstype', $params['businesstype'])
            ->where('idnumber', $params['idnumber'])
            ->find();
        if ($yw) {
            $total = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                ->where('paymentdate', '>=', $paymentdate)
                ->where('paymentdate', '<=', $paymentdate2)
                ->where('refundornot', '')
                ->count();

            if ($total > 0) {
                $this->error(__('系统已存在同一客户同一业务相同的缴纳年月!'));
            }
        }

        $params = $this->setquanxianlog($params);

        $result = $this->model->allowField(true)->save($params);

        $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $params['idnumber']]);
        if ($kehu) {
            $kehu->name = $params['name'];
            $kehu->mobile = $params['mobile'];
            $kehu->save();
        } else {
            $kehu = new \app\admin\model\dahui\Kehu;
            $result2 = $kehu->allowField(true)->save($params);
        }

        echo "addneworder";
    }


    //http://dahui.test/api/dahui/getuser?openid=111
    public function getuser($openid) {

        if (empty($openid)) {
            $this->error('openid不能为空！');
        }

        $kehu = \app\admin\model\dahui\Kehu::where('openid', $openid)->field("idnumber, name, mobile")->find();
        if (!$kehu) {
            $this->error('openid关联不到客户！');
        }

        $this->success('返回成功', $kehu);
    }

    //http://dahui.test/api/dahui/postuser
    public function postuser()
    {
        $idnumber = $this->request->request('idnumber', "");
        $openid = $this->request->request('openid', "");
        $idcard0_image = $this->request->request('idcard0_image', "");
        $idcard1_image = $this->request->request('idcard1_image', "");
        $address0 = $this->request->request('address0', "");
        $address1 = $this->request->request('address1', "");

        $khuh = $this->request->request('khuh', "");
        $mobile = $this->request->request('mobile', "");
        $bankcardnumber = $this->request->request('bankcardnumber', "");

        if (empty($idnumber)) {
            $this->error('idnumber不能为空！');
        }

        if (empty($openid)) {
            $this->error('openid不能为空！');
        }

        $kehu = \app\admin\model\dahui\Kehu::where('idnumber', $idnumber)->find();
        if (!$kehu) {
            $kehu = new \app\admin\model\dahui\Kehu;
            $kehu->idnumber = $idnumber;
        }
        $kehu->openid = $openid;
        $kehu->idcard0_image = $idcard0_image;
        $kehu->idcard1_image = $idcard1_image;
        $kehu->address0 = $address0;
        $kehu->address1 = $address1;
        if (!empty($khuh)) {
            $kehu->khuh = $khuh;
        }
        if (!empty($mobile)) {
            $kehu->mobile = $mobile;
        }
        if (!empty($bankcardnumber)) {
            $kehu->bankcardnumber = $bankcardnumber;
        }
        $kehu->save();

        $this->success('返回成功');
    }

//INSERT INTO `fa_dahui_dingdan` (`businesstype` , `taobaoname` , `idnumber` , `paymentnumber` , `payingcompany` , `paymentbase` , `paymentamount` , `paymentdate` , `agencyfees` , `ordercomment` , `newkehu` , `orderdate` , `auditman`)
//VALUES ('社保' , 'sfdas' , '450204197710290615' , '1213123123' , '达慧' , 5975 , 2345.6 , '2021-11' , '付代理费' , '订单备注' , '是' , '2021-11-08 10:39:22' , 'Admin')
//SELECT * FROM `fa_dahui_kehu` WHERE  `idnumber` = '450204197710290615' LIMIT 1
//INSERT INTO `fa_dahui_kehu` (`idnumber` , `name` , `mobile` , `auditman`) VALUES ('450204197710290615' , 'colin' , '13564508883' , 'Admin')
    //http://dahui.test/api/dahui/postorder
    public function postorder()
    {
        $businesstypes = $this->request->request('businesstypes', "");
        $taobaoname = $this->request->request('taobaoname', "");
        $idnumber = $this->request->request('idnumber', "");
        $paymentnumber = $this->request->request('paymentnumber', "");
        $payingcompany = $this->request->request('payingcompany', "");
        $paymentbase = $this->request->request('paymentbase', 2485);
        $paymentamount = $this->request->request('paymentamount', "");
        $paymentdate = $this->request->request('paymentdate', "");
        $agencyfees = $this->request->request('agencyfees', "");
        $ordercomment = $this->request->request('ordercomment', "");
        $openid = $this->request->request('openid', "");

        $idcard0_image = $this->request->request('idcard0_image', "");
        $idcard1_image = $this->request->request('idcard1_image', "");
        $address0 = $this->request->request('address0', "");
        $address1 = $this->request->request('address1', "");

        $khuh = $this->request->request('khuh', "");
        $mobile = $this->request->request('mobile', "");
        $bankcardnumber = $this->request->request('bankcardnumber', "");

        $orderdate = date('Y-m-d H:i:s');
        $auditman = "wx";

        $params = array(
            'businesstype' => '',
            'taobaoname' => $taobaoname,
            'idnumber' => $idnumber,
//            'paymentnumber' => $paymentnumber,
//            'payingcompany' => $payingcompany,
//            'paymentbase' => $paymentbase,
//            'paymentamount' => $paymentamount,
            'paymentdate' => $paymentdate,
//            'agencyfees' => $agencyfees,
//            'ordercomment' => $ordercomment,
            'orderdate' => $orderdate,
            'auditman' => $auditman,
        );


        if (empty($businesstypes)) {
            $this->error('businesstypes不能为空！');
        }

        if (empty($taobaoname)) {
            $this->error('taobaoname不能为空！');
        }

        if (empty($idnumber)) {
            $this->error('idnumber不能为空！');
        }

        if (empty($paymentdate)) {
            $this->error('paymentdate不能为空！');
        }

        $businesstypes = explode(",", $businesstypes);
        for ($i = 0; $i < count($businesstypes); $i++) {
            if ($businesstypes[$i] == '待发工资') {
                continue;
            }
            $params['businesstype'] = $businesstypes[$i];
            $model = new \app\admin\model\dahui\Dingdan;
            $total = $model
                ->where('businesstype', $params['businesstype'])
                ->where('idnumber', $params['idnumber'])
                ->where('auditstatus', '未审核')
                ->where('paymentdate', 'like', '%'.$params['paymentdate'].'%')
                ->count();

            if ($total > 0) {
                $this->error("系统已录入此{$params['businesstype']}订单!");
            }
        }

        $ok = "";
        for ($i = 0; $i < count($businesstypes); $i++) {
            if ($businesstypes[$i] == '待发工资') {
                continue;
            }
            $params['businesstype'] = $businesstypes[$i];
            if (!$this->addOrder($params) ) {
                $this->error("已有缴纳记录，添加{$params['businesstype']}订单失败！[已成功的订单]".$ok);
            }
            $ok .= $params['businesstype'].",";
        }

        $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $params['idnumber']]);
        if (!$kehu) {
            $kehu = new \app\admin\model\dahui\Kehu;
            $kehu->idnumber = $idnumber;
        }
        $kehu->openid = $openid;
        $kehu->idcard0_image = $idcard0_image;
        $kehu->idcard1_image = $idcard1_image;
        $kehu->address0 = $address0;
        $kehu->address1 = $address1;

        if (!empty($khuh)) {
            $kehu->khuh = $khuh;
        }
        if (!empty($mobile)) {
            $kehu->mobile = $mobile;
        }
        if (!empty($bankcardnumber)) {
            $kehu->bankcardnumber = $bankcardnumber;
        }
        
        $kehu->save();

        $this->success('返回成功');
    }

    public function addorder($params)
    {
        $model = new \app\admin\model\dahui\Dingdan;
        $dates = explode(" ~ ", $params['paymentdate']);

        $paymentdate = $dates[0];
        $paymentdate2 = $paymentdate;
        if (count($dates) > 1) {
            $paymentdate2 = $dates[1];
        }

        if (isset($params['taobaoname'])) {
            $params['taobaoname'] = trim($params['taobaoname']);
        }

        $yw = \app\admin\model\dahui\Yewu::where('businesstype', $params['businesstype'])
            ->where('idnumber', $params['idnumber'])
            ->find();
        if ($yw) {
            $total = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                ->where('paymentdate', '>=', $paymentdate)
                ->where('paymentdate', '<=', $paymentdate2)
                ->where('refundornot', '')
                ->count();

            if ($total > 0) {
                return false;
            }
        }

        $result = $model->allowField(true)->save($params);
        return true;
    }

//select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber  inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber where ny.paymentdate = '2021-10'  and ny.newjoin_status <> '办理失败'  and ny.refundornot  = ''   and (ny.zc_status  = '已办理' or not exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '2021-11' and payingcompany = ny.payingcompany and refundornot = '' ) )
    //http://dahui.test/api/dahui/postzhuanchu?idnumber=110101198002062533&paymentdate=2021-12&businesstype=社保&zc_note=退工时间的转出备注
    public function postzhuanchu()
    {
        $name = $this->request->request('name', "");
        $idnumber = $this->request->request('idnumber', "");
        $paymentdate = $this->request->request('paymentdate', "");//转出月份
        $xgsrzsj = $this->request->request('xgsrzsj', "");//新公司入职时间
        $tgsj = $this->request->request('tgsj', "");//退工时间
        $businesstypes = $this->request->request('businesstypes', "");

        if (empty($businesstypes)) {
            $this->error('businesstypes不能为空！');
        }

        if (empty($idnumber)) {
            $this->error('idnumber不能为空！');
        }

        if (empty($paymentdate)) {
            $this->error('paymentdate不能为空！');
        }

        $kehu = \app\admin\model\dahui\Kehu::where('idnumber', $idnumber)->find();
        if (!$kehu) {
            $this->error('idnumber关联不到客户！');
        }

        if (strcmp($name, $kehu->name) != 0 ) {
            $this->error('身份证号和参保人姓名不匹配！');
        }

        $ok = "";
        $businesstypelist = explode(",", $businesstypes);
        for ($i = 0; $i < count($businesstypelist); $i++) {
            $yewu = \app\admin\model\dahui\Yewu::where('businesstype', $businesstypelist[$i])->where('idnumber', $idnumber)->find();
            if (!$yewu) {
                $this->error("身份证号关联不到{$businesstypelist[$i]}历史业务！[转出成功的业务]".$ok);
            }

            $ny = \app\admin\model\dahui\Nianyue::where('businessnumber', $yewu->businessnumber)->where('paymentdate', $paymentdate)->find();
            if (!$ny) {
                $this->error('转出日期关联不到！[转出成功的业务]'.$ok);
            }

            if ($ny->zc_status == "已办理") {
                $this->error('重复转出！');
            }
            $ny->earlytransfer = "是";
            $ny->xgsrzsj = $xgsrzsj;
            $ny->tgsj = $tgsj;
            $ny->zc_note = "[转出日期]".date('Y-m-d H:i:s');
            $ny->save();

            $ok .= $businesstypelist[$i].",";
        }

        $this->success('返回成功');
    }

    //http://dahui.test/api/dahui/tryaudit
    //检查未审核的订单，是否已经审核过了
    public function tryaudit()
    {
        set_time_limit(0); //执行时间无限
        ini_set('memory_limit', '-1'); //内存无限

        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no');

        $list = \app\admin\model\dahui\Dingdan::where('create_time', '>', date('Y-m-d'))
            ->where('auditstatus', '未审核')->field('id,create_time,auditstatus,auditman')->select();

//        $this->success('返回成功', $list);
        foreach ($list as &$row) {
            $ny = \app\admin\model\dahui\Nianyue::where('dingdan_id', $row['id'])->find();

            if ($ny) {
                echo $row['id']."<br>";
                $row['auditstatus'] = '已审核';
                $row['auditman'] = $ny['auditman'];
                $row->save();
            }

        }

        echo "ok<br>";

    }

    //http://dahui.test/api/dahui/trynoaudit
    //检查已审核的订单，是否有缴纳记录
    public function trynoaudit()
    {
        set_time_limit(0); //执行时间无限
        ini_set('memory_limit', '-1'); //内存无限

        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no');

        $list = \app\admin\model\dahui\Dingdan::where('create_time', '>', date('Y-m-d'))
            ->where('auditstatus', '已审核')->field('id,create_time,auditstatus,auditman')->select();

        foreach ($list as &$row) {
            $ny = \app\admin\model\dahui\Nianyue::where('dingdan_id', $row['id'])->find();

            if (!$ny) {
                echo "<br>".$row['id']."<br>";
            } else {
                echo $row['id']."-";
            }

        }

        echo "ok<br>";

    }

    //http://dahui.test/api/dahui/test16
    public function test16()
    {

//        $list1 = \app\admin\model\dahui\Gongzi::field("id, auditstatus,ordernumber", true)
////          ->field("gongzi.id,gongzi.auditstatus,gongzi.preprocessing,gongzi.orderdate,gongzi.opyear,gongzi.taobaoname,gongzi.ownedcompany,gongzi.idnumber,gongzi.payyear,gongzi.agencyfees,gongzi.sbbase,gongzi.gjjbase,gongzi.totalwages,gongzi.r_wages,kehu.name, kehu.mobile,kehu.bankcardnumber ")
//            ->limit(1)
//            ->select();

        $list = \app\admin\model\dahui\Gongzi::field("id, auditstatus, idnumber")
            ->with(['kehu'])
//          ->field("gongzi.id,gongzi.auditstatus,gongzi.preprocessing,gongzi.orderdate,gongzi.opyear,gongzi.taobaoname,gongzi.ownedcompany,gongzi.idnumber,gongzi.payyear,gongzi.agencyfees,gongzi.sbbase,gongzi.gjjbase,gongzi.totalwages,gongzi.r_wages,kehu.name, kehu.mobile,kehu.bankcardnumber ")
            ->limit(1)
            ->select();

        $this->success('返回成功', $list);
    }

    //http://dahui.test/api/dahui/test17
    public function test17()
    {
        $nymodel = new \app\admin\model\dahui\Qiyenianyue();

        $ny = $nymodel->where('businessnumber', "678")
            ->where('yearpayment', "2022-06")
            ->where('type', '<>', '转出')
            ->find();

        $this->success('返回成功', $ny);
    }

    //http://dahui.test/api/dahui/zrxy
    //定时，每月1日 05:00 转入
    public function zrxy()
    {
        set_time_limit(0); //执行时间无限
        ini_set('memory_limit', '-1'); //内存无限

        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no');

        $nextmoth = date("Y-m");
        $thismoth = date("Y-m", strtotime("-1 months", strtotime($nextmoth)));

        $sql = "truncate table aa";
        Db::execute($sql);

        $sql = "insert into aa (select businessnumber, '{$nextmoth}' as yearpayment, payingcompany, paymentbase, paymentamount, ''note,
case when CONCAT(type,status)='新入办理失败' then '新入' else '续费' end type, '未办理'status
from fa_dahui_qiyenianyue where yearpayment='{$thismoth}' and type!='转出');";
        Db::execute($sql);

        $sql = "delete from aa where exists (select businessnumber from fa_dahui_qiyenianyue where businessnumber=
aa.businessnumber and yearpayment= aa.yearpayment)";
        Db::execute($sql);

        $sql = "insert into fa_dahui_qiyenianyue (businessnumber,yearpayment, payingcompany, paymentbase, paymentamount, note, type)
select businessnumber,yearpayment, payingcompany, paymentbase, paymentamount, note, type
from aa where not exists (select businessnumber from fa_dahui_qiyenianyue where businessnumber=
aa.businessnumber and yearpayment= aa.yearpayment)";
        Db::execute($sql);

//        $this->success("批量客户转入");
        echo "ok<br>";
    }

    //http://dahui.test/api/dahui/getuserinfo?idnumber=130230198105213016
    public function getuserinfo($idnumber)
    {
        $kehu = \app\admin\model\dahui\Qiyekehu::get(['idnumber' => $idnumber]);
        if (!$kehu) {
            $this->error("获取用户信息失败！");
        }
        $this->success('获取用户信息成功！', $kehu);
    }


    //http://dahui.test/api/dahui/checknewjl
    //检查fa_dahuinew_dljzyw+fa_dahuis_sbgjj的fa_dahuinew_dljzmonth +fa_dahuis_sbgjjmonth
    public function checknewjl()
    {
        set_time_limit(0); //执行时间无限
        ini_set('memory_limit', '-1'); //内存无限

        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no');

        $num = 0;
        $list = \app\admin\model\dahuinew\Dljzyw::all();
        foreach ($list as $row) {
            $from = $row['htstart'];
            $to = $row['htend'];
            $newny = $from;
            for ($i = 0; $i < 48; $i++) {
                $ny = \app\admin\model\dahuinew\Dljzmonth::where("dljzyw_id", $row->id)->where("sbyf", $newny)->find();
                if (!$ny) {
                    $ny = new \app\admin\model\dahuinew\Dljzmonth;
                    $ny->sbyf = $newny;
                    $ny->zczt = "未办理";
                }
                $ny->jbry = $row['jbry'];
                $ny->dljzyw_id = $row->id;
                $ny->save();
                $newny = date("Y-m", strtotime("+1 months", strtotime($newny)));
                if ($newny > $to) {
                    break;
                }
            }
            $num++;
            echo "Dljzyw = ".$num."  <br>";
        }

        $num=0;
        $list = \app\admin\model\dahuis\Sbgjj::all();
        foreach ($list as $row) {
            $from = $row['htstart'];
            $to = $row['htend'];
            $newny = $from;
            for ($i = 0; $i < 48; $i++) {
                $ny = \app\admin\model\dahuis\Sbgjjmonth::where("sbgjj_id", $row->id)->where("sbyf", $newny)->find();
                if (!$ny) {
                    $ny = new \app\admin\model\dahuis\Sbgjjmonth;
                    $ny->sbyf = $newny;
                    $ny->zczt = "未办理";
                }
                $ny->sbgjj_id = $row->id;
                $ny->save();

                $newny = date("Y-m", strtotime("+1 months", strtotime($newny)));
                if ($newny > $to) {
                    break;
                }
            }
            $num++;
            echo "Sbgjj=".$num."  <br>";
        }

        echo "ok<br>";

    }

    //http://dahui.test/api/dahui/createyzm?name=阿斯顿发送到
    public function createyzm($name)
    {
        $yzm = md5(trim($name)."colin");
        $this->success('获取公司验证码成功！', strtoupper(substr($yzm, -4)));
    }

    //http://dahui.test/api/dahui/setkpny
    //设置开票年月
    public function setkpny()
    {
        $list = \app\admin\model\kpxt\Kpinfo::all();
        foreach ($list as &$row) {
            $row->kpny = substr($row['sqriqi'], 0, 7);
            $row->save();
        }
        echo "ok<br>";
    }


    //http://dahui.test/api/dahui/dljznot?ny=2023-07
    //代理记账本月申报无的订单
    public function dljznot()
    {
        $thismonth = $this->request->request('ny', date("Y-m"));
        //select * from fa_dahuinew_dljzyw where htstart > '2023-07' or htend2 < '2023-07'
        $list = \app\admin\model\dahuinew\Dljzyw::where('htstart', '>', $thismonth)->whereOr('htend2', '<', $thismonth)->select();
        foreach ($list as $row) {
            echo "合同编号[{$row->htbh}] 合同开始[{$row->htstart}] 合同结束[{$row->htend2}] 公司[{$row->gsname}]<br>";
        }
//        echo "ok<br>";
//        $this->success('代理记账本月申报无的订单！', $list);
    }


    //http://dahui.test/api/dahui/xzdljzyw
    //修正代理记账订单
    public function xzdljzyw()
    {
        set_time_limit(0); //执行时间无限
        ini_set('memory_limit', '-1'); //内存无限
        ob_end_clean();
        ob_implicit_flush();
        header('X-Accel-Buffering: no');

        echo '现在是：' . date('H:i:s') . '<br>';

        $num = 0;
        /*
        //修正htend2
        $list = \app\admin\model\dahuinew\Dljzyw::all();
        foreach ($list as &$row) {
            if ($row->htend == $row->htend2) {
                $row->htend2 = date("Y-m", strtotime("+1 months", strtotime($row->htend)));
                $row->save();
                $num++;
                if ($num % 10 == 1) {
                    echo $num . '<br>';
                }
            }
        }


        //pid
        $list = \app\admin\model\dahuinew\Dljzyw::where('htbh', '')->select();
        foreach ($list as &$row) {
            $pd = \app\admin\model\dahuinew\Dljzyw::where('htbh', '<>', '')->where('gsname', $row->gsname)->find();
            if ($pd) {
                if ($pd->pid > 0) {
                    $row->pid = $pd->pid;
                } else {
                    $row->pid = $pd->id;
                }
                $num++;
                $row->save();
            }
            if ($num % 10 == 1) {
                echo $num . '<br>';
            }
        }
 */

        $list = \app\admin\model\dahuinew\Dljzyw::where('isxy', '已续约')->select();
        foreach ($list as &$row) {
            $pd = \app\admin\model\dahuinew\Dljzyw::where('pid', $row->id)->find();
            if (!$pd) {
                echo $row->id .' '.$row['gsname']. '<br>';
                $row->isxy = '未续约';
                $row->save();
            }
        }

        echo 'OK--' . date('H:i:s') . '<br>';
    }
}


/*
UPDATE fa_dahui_nianyue
SET dingdan_id = 0
WHERE dingdan_id > 0

select * from fa_dahui_dingdan where newkehu <> '是' limit 100


UPDATE fa_dahui_qiyenianyue A, aa B
SET A.type = B.type
WHERE A.businessnumber = B.businessnumber and A.yearpayment = B.yearpayment

UPDATE fa_dahui_qiyeputong A, fa_dahui_qiyenianyue B
SET A.payingcompany = B.payingcompany
WHERE A.businessnumber = B.businessnumber

UPDATE fa_dahui_nianyue
SET earlytransfer = '否'
WHERE earlytransfer <> '是'

*/